/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.absisgroup.nt;

import com.absisgroup.nsc.utilities.GenericServlet;
import com.absisgroup.nsc.utilities.Util;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.Date;
import java.util.Properties;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 *
 * @author Hue
 */
public class XemPhieuBanHang extends GenericServlet {

    @Override
    public int process(final Connection con, final HttpServletRequest request, final HttpServletResponse response) throws Exception {
        BigDecimal orderPK = Util.toBigDecimal(request.getParameter("orderPK"));

        writeToResponse(response, toJSONObject(con, orderPK));

        return DONT_RUN_JSP;
    }

    private String toJSONObject(final Connection con, BigDecimal orderPK) throws Exception {
        final JSONObject jsObj = new JSONObject();
        
        PreparedStatement pStmt = con.prepareStatement("select ORDER_DATE, ORDER_CUSTOMER, ORDER_ADDRESS, ORDER_DEPOSIT, ORDER_PHONE, ORDER_SELLER, ORDER_NO, ORDER_DELIVERY from ORDERS where ORDER_PK = ?");
        pStmt.setBigDecimal(1, orderPK);
        ResultSet rs = pStmt.executeQuery();
        if(rs.next()) {
            Properties texts = Util.getAppTexts(getServletContext());
            String dateText = texts.getProperty("NGAY_THANG_NAM");
            Calendar cal = Calendar.getInstance();
            cal.setTime(rs.getDate("ORDER_DATE"));
            dateText = Util.replace(dateText, "{0}", String.valueOf(cal.get(Calendar.DATE)));
            dateText = Util.replace(dateText, "{1}", String.valueOf(cal.get(Calendar.MONTH) + 1));
            dateText = Util.replace(dateText, "{2}", String.valueOf(cal.get(Calendar.YEAR)));

            jsObj.put("dateText", dateText);
            jsObj.put("customer", rs.getString("ORDER_CUSTOMER"));
            jsObj.put("address", rs.getString("ORDER_ADDRESS"));
            jsObj.put("deposit", rs.getLong("ORDER_DEPOSIT"));
            jsObj.put("phone", rs.getString("ORDER_PHONE"));
            jsObj.put("seller", rs.getString("ORDER_SELLER"));
            jsObj.put("deliveryDate", getDeliveryDateText(texts, rs.getTimestamp("ORDER_DELIVERY")));
            jsObj.put("orderNo", Util.replace(texts.getProperty("SO_PHIEU"), "{0}", rs.getString("ORDER_NO")));
        }
        pStmt.close();

        JSONArray records = getDetails(con, orderPK);

        jsObj.put("records", records);
        if(orderPK != null) {
            jsObj.put("orderPK", orderPK.longValue());
        }
        jsObj.put("success", true);

        return jsObj.toString();
    }
    
    private String getDeliveryDateText(Properties texts, Date date) {
        if(date == null) {
            return "";
        }
        String dateText = texts.getProperty("NGAY_THANG_NAM_GIO_PHUT");
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        dateText = Util.replace(dateText, "{0}", String.valueOf(cal.get(Calendar.HOUR_OF_DAY)));
        dateText = Util.replace(dateText, "{1}", String.valueOf(cal.get(Calendar.MINUTE)));
        dateText = Util.replace(dateText, "{2}", String.valueOf(cal.get(Calendar.DATE)));
        dateText = Util.replace(dateText, "{3}", String.valueOf(cal.get(Calendar.MONTH) + 1));
        dateText = Util.replace(dateText, "{4}", String.valueOf(cal.get(Calendar.YEAR)));
        return dateText;
    }
    
    @Override
    protected boolean useConnection() {
        return true;
    }
    
    private JSONArray getDetails(Connection con, BigDecimal orderPK) throws Exception {
        JSONArray records = new JSONArray();
        String strQuery = "select ORD_DETAILS_STT, PROD_FOR_SELL, ORD_DETAILS_DVT, ORD_DETAILS_CHIEU_DAI, ORD_DETAILS_SO_TAM, ORD_DETAILS_SO_LUONG, ORD_DETAILS_DON_GIA, ORD_DETAILS_GHI_CHU " + 
                          "from ORDERS_DETAILS, PRODUCTS " +
                          "where ORD_DETAILS_PROD_FK = PROD_PK and ORD_DEATAILS_ORDERS_FK = ? " + 
                          "order by ORD_DETAILS_STT";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        pStmt.setBigDecimal(1, orderPK);
        int iiIndex = 1;
        ResultSet rs = pStmt.executeQuery();
        while(rs.next()) {
            JSONObject jsObj = new JSONObject();
            jsObj.put("stt", iiIndex++);
            jsObj.put("tenMH", rs.getString("PROD_FOR_SELL"));
            jsObj.put("dvt", rs.getString("ORD_DETAILS_DVT"));
            jsObj.put("chieuDai", rs.getBigDecimal("ORD_DETAILS_CHIEU_DAI"));
            jsObj.put("soTam", rs.getBigDecimal("ORD_DETAILS_SO_TAM"));
            BigDecimal soLuong = rs.getBigDecimal("ORD_DETAILS_SO_LUONG");
            long donGia = rs.getLong("ORD_DETAILS_DON_GIA");
            jsObj.put("soLuong", soLuong);
            jsObj.put("donGia", donGia);
            if(soLuong != null) {
                jsObj.put("thanhTien", soLuong.multiply(new BigDecimal(donGia)));
            }
            jsObj.put("ghiChu", rs.getString("ORD_DETAILS_GHI_CHU"));

            records.put(jsObj);
        }
        pStmt.close();
        
        return records;
    }
}
